package com.huatai.bpmn.mapper;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.huatai.bpmn.entity.BasicLoadEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 *  表单数据
 * </p>
 *
 * @author yxy
 * @since 2023-07-07
 */
@Mapper
public interface BasicLoadMapper extends BaseMapper<BasicLoadEntity> {
	@Select("SET @makabaka = NULL; " +
		"SELECT GROUP_CONCAT( " +
		"DISTINCT CONCAT( ' MAX(IF(a.json_key = ''', a.json_key, ''', json_val, NULL)) AS ''', a.json_key, '''' )) INTO @makabaka  " +
		"FROM pmp_basic a  WHERE a.place like '%${place}%'; " +
		"SET @makabaka = CONCAT( '  Select any_value(flow_id) as flowId,any_value(place) as place,', @makabaka, ' from " +
		"pmp_basic_load a where place=\"${place}\" Group by a.flow_id limit ${pgIndex},${pgSize}' ); " +
		"PREPARE stmt  " +
		"FROM @makabaka; " +
		"EXECUTE stmt; " +
		"DEALLOCATE PREPARE stmt;")
	List<JSONObject> pageList(@Param("place") String place, @Param("pgIndex") int pgIndex, @Param("pgSize") int pgSize);

	@Select("SET @makabaka = NULL; " +
		"SELECT GROUP_CONCAT( " +
		"DISTINCT CONCAT( ' MAX(IF(a.json_key = ''', a.json_key, ''', json_val, NULL)) AS ''', a.json_key, '''' )) INTO @makabaka  " +
		"FROM pmp_basic a  WHERE a.place like '%${place}%'; " +
		"SET @makabaka = CONCAT( '  Select any_value(a.business_key) as businessKey,any_value(flow_id) as flowId,', @makabaka, ' from " +
		"pmp_basic_load a where a.business_key=\"${businessKey}\" Group by a.flow_id' ); " +
		"PREPARE stmt  " +
		"FROM @makabaka; " +
		"EXECUTE stmt; " +
		"DEALLOCATE PREPARE stmt;")
	List<JSONObject> listByPlaceAndBusinessKey(@Param("place") String placce,@Param("businessKey")String businessKey);

	@Select("SET @makabaka = NULL; " +
		"SELECT GROUP_CONCAT( " +
		"DISTINCT CONCAT( ' MAX(IF(a.json_key = ''', a.json_key, ''', json_val, NULL)) AS ''', a.json_key, '''' )) INTO @makabaka  " +
		"FROM pmp_basic a  WHERE a.place like '%${place}%'; " +
		"SET @makabaka = CONCAT( '  Select any_value(flow_id) as flowId,any_value(place) as place,', @makabaka, ' from " +
		"pmp_basic_load a where place=\"${place}\" Group by a.flow_id' ); " +
		"PREPARE stmt  " +
		"FROM @makabaka; " +
		"EXECUTE stmt; " +
		"DEALLOCATE PREPARE stmt;")
    List<JSONObject> count(String place);

	@Select("select place from pmp_basic_load where business_key=\"${businessKey}\" GROUP BY place")
	List<String> listByPlace(String businessKey);

	@Select("select json_key,json_val from pmp_basic_load where place=\"${place}\" and flow_id=\"${flowId}\" and json_key in ${finalStr}")
    List<JSONObject> listByFlowId(String flowId, String finalStr, String place);

	@Select("select ${finalBasicStr} from pmp_basic_load where place=\"${place}\"" +
		" and business_key=\"${business_key}\" and json_key in ${finalStr}")
	JSONObject listFlowIds(String business_key, String finalStr, String place,String finalBasicStr);




		@Select("SET @makabaka = NULL; " +
			"SELECT GROUP_CONCAT( " +
			"DISTINCT CONCAT( ' MAX(IF(a.json_key = ''', a.json_key, ''', json_val, NULL)) AS ''', a.json_key, '''' )) INTO @makabaka  " +
			"FROM yy_basic a  WHERE a.place like '%${place}%'; " +
			"SET @makabaka = CONCAT( '  Select ', @makabaka, ' from " +
			"pmp_basic_load a where a.business_key=\"${businessKey}\"' ); " +
			"PREPARE stmt  " +
			"FROM @makabaka; " +
			"EXECUTE stmt; " +
			"DEALLOCATE PREPARE stmt;")
	JSONObject getParamData(String businessKey, String place);



	@Select("SET @makabaka = NULL; " +
		"SELECT GROUP_CONCAT( " +
		"DISTINCT CONCAT( ' MAX(IF(a.json_key = ''', a.json_key, ''', json_val, NULL)) AS ''', a.json_key, '''' )) INTO @makabaka  " +
		"FROM yy_basic a  WHERE a.place like '%${place}%'; " +
		"SET @makabaka = CONCAT( '  Select a.flow_id,', @makabaka, ' from " +
		"pmp_basic_load a where a.business_key=\"${businessKey}\" and a.place=\"${place}\" GROUP BY a.flow_id' ); " +
		"PREPARE stmt  " +
		"FROM @makabaka; " +
		"EXECUTE stmt; " +
		"DEALLOCATE PREPARE stmt;")
	List<JSONObject> getColumnData(String businessKey, String place);





	@Select("select COALESCE(sum(json_val),0) from pmp_basic_load where json_key=\"${jsonKey}\" and business_key in ${businessKey}")
	double getCountMoney(String jsonKey, String businessKey);

	@Select("select business_key,${basicStr} from pmp_basic_load where business_key in ${str} GROUP BY business_key")
	List<JSONObject> getCountList(String basicStr, String str);

	@Select("select DISTINCT MAX(IF(json_key = \"gkbm\", json_val, NULL)) AS \"gkbm\" from pmp_basic_load where business_key in ${str} GROUP BY business_key")
	List<String> getDeptCountList(String str);

	@Select("select flow_id from yy_data_info where my_key=\"shangjichuangjian\" and YEAR(create_time)=${year}")
	List<String> getSJList(int year);
//	@Select("select flow_id from yy_data_info where my_key=\"ruku\" and YEAR(create_time)=${year}")
	@Select("select flow_id from yy_data_info where my_key=\"ruku\"")
	List<String> getRkList(int year);
	@Select("select  MAX(IF(json_key = \"${key}\", json_val, NULL)) AS \"${key}\" from pmp_basic_load where business_key in ${str} and json_val=\"${data}\" GROUP BY business_key")
	List<String> getKeyCount(String key,String data, String str);

	@Select("select COALESCE(sum(temp.${key}),0) from (select  MAX(IF(json_key = \"${key}\", json_val, NULL)) AS \"${key}\" from pmp_basic_load " +
		"where business_key in ${str} and json_val=\"新增\" GROUP BY business_key) temp")
	String getCountAddByMoney(String key, String str);
	@Select("select COALESCE(sum(temp.${key}),0) from (select  MAX(IF(json_key = \"${key}\", json_val, NULL)) AS \"${key}\" from pmp_basic_load " +
		"where business_key in ${str} GROUP BY business_key) temp")
	String getCountByMoney(String key, String str);

	@Select("select MAX(IF(json_key = \"xmlx\", json_val, NULL)) AS \"xmlx\",MAX(IF(json_key = \"yjje\", json_val, NULL)) AS \"yjje\"" +
		",MAX(IF(json_key = \"yjldje\", json_val, NULL)) AS \"yjldje\",MAX(IF(json_key = \"yjys\", json_val, NULL)) AS \"yjys\",MAX(IF(json_key =" +
		" \"yjhk\", json_val, NULL)) AS \"yjhk\",MAX(IF(json_key = \"xmdj\", json_val, NULL)) AS \"xmdj\" from pmp_basic_load where business_key in " +
		"${str} GROUP BY business_key;")
	List<JSONObject> getRkListByMoney(String str);

//	@Select("select a.flow_id from yy_data_info a RIGHT JOIN pmp_basic_load b on  a.flow_id=b.business_key and a.my_key=\"shangjichangjian\" and b.json_key=\"gkbm\" and json_val=\"${deptName}\" and YEAR(a.create_time)=${year} GROUP BY a.flow_id")
	@Select("select business_key from pmp_basic_load WHERE business_key in (select flow_id from yy_data_info where my_key=\"shangjichuangjian\" and YEAR(create_time)=${year}) and json_key=\"gkbm\" and json_val=\"${deptName}\" GROUP BY business_key;")
	List<String> getSJDeptList(int year,String deptName);


//	@Select("select business_key from pmp_basic_load WHERE business_key in (select flow_id from yy_data_info where my_key=\"ruku\" and YEAR(create_time)=${year}) and json_key=\"gkbm\" and json_val=\"${deptName}\" GROUP BY business_key;")
	@Select("select business_key from pmp_basic_load WHERE business_key in (select flow_id from yy_data_info where my_key=\"ruku\") and json_key=\"gkbm\" and json_val=\"${deptName}\" GROUP BY business_key;")
	List<String> getRkDeptList(int year, String deptName);

	@Select(" Select ${basicStr} from pmp_basic_load a where a.business_key=\"${businessKey}\"")
	JSONObject getParamData1(String businessKey, String basicStr);

	@Select("select place from pmp_basic_load  WHERE flow_id=\"${flowId}\" GROUP BY place")
	String getPlace(String flowId);

	@Update("update pmp_basic_load set place=\"${place}\" where " +
		"business_key=\"${businessKey}\" and place=\"sjjbxx\"")
	void updatePlaceByBus(String place, String businessKey);

	@Select("select flow_id from yy_data_info where my_key=\"${myKey}\" and YEAR(create_time)=${year}")
	List<String> getTDList(int year,String myKey);

	@Select("select business_key from pmp_basic_load WHERE business_key in (select flow_id from yy_data_info where my_key=\"${myKEY}\" and YEAR(create_time)=${year}) and json_key=\"gkbm\" and json_val=\"${deptName}\" GROUP BY business_key;")
	List<String> getTDDeptList(int year, String deptName, String myKEY);


	/**
	 * 部门老大根据归口部门查询数据
	 * @return
	 */
	@Select("select a.flow_id from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" and b.json_key=\"gkbm\" and b.json_val=\"${deptName}\" limit ${pgIndex},${pgSize}")
	List<String> getFlowList(String myKey, String deptName,int pgIndex,int pgSize);

	@Select("select COALESCE(count(a.flow_id),0) from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" and b.json_key=\"gkbm\" and b.json_val=\"${deptName}\"\n")
	int getFlowListCount(String myKey, String deptName);
	@Select("select a.flow_id from yy_data_info a LEFT JOIN pmp_basic_load b " +
		"on a.flow_id=b.business_key where a.source=\"${myKey}\" and b.json_key=\"gkbm\" " +
		"and b.json_val=\"${deptName}\" order by a.create_time limit ${pgIndex},${pgSize}")
	List<String> getFlowListByMyKey(String myKey, String deptName, int pgIndex, int pgSize);
	@Select("select COALESCE(count(a.flow_id),0) from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key where a.source=\"${myKey}\" and b.json_key=\"gkbm\" and b.json_val=\"${deptName}\"\n")
	int getFlowListCountByMyKey(String myKey, String deptName);




	/**
	 * 员工查询数据
	 * @return
	 */
	@Select("select a.flow_id from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key where a.source=\"${myKey}\"" +
		" and b.json_key=\"xmjl\" and b.json_val=\"${getRealName}\" order by create_time desc limit ${pgIndex},${pgSize}")
	List<String> getFlowList1ByMyKey(String myKey, String getRealName,int pgIndex,int pgSize);

	@Select("select COALESCE(count(a.flow_id),0) from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key" +
		" where a.source=\"${myKey}\" and b.json_key=\"xmjl\" and b.json_val=\"${getRealName}\"")
	int getFlowList1CountByMyKey(String myKey, String getRealName);

	@Update("update pmp_basic_load set place=\"${place}\" where " +
		"business_key=\"${businessKey}\" and place=\"xmjbxx\"")
	void updatePlaceByBusByHMD(String place, String businessKey);

	@Select("SELECT a.flow_id FROM yy_data_info a LEFT JOIN (" +
		"SELECT * FROM (SELECT a.business_key,MAX(IF( a.json_key = 'gkbm', a.json_val, NULL )) AS 'gkbm',MAX(IF( a.json_key = 'xmmc'," +
		" a.json_val, NULL )) AS 'xmmc',MAX(IF( a.json_key = 'xmdj', a.json_val, NULL )) AS 'xmdj'," +
		"MAX(IF( a.json_key = 'yjqdsj', a.json_val, NULL )) AS 'yjqdsj',MAX(IF( a.json_key = 'xmjl', a.json_val, NULL )) AS 'xmjl' " +
		"FROM pmp_basic_load a GROUP BY a.business_key) temp WHERE temp.gkbm = \"${deptName}\") b ON a.flow_id = b.business_key " +
		"WHERE a.source = \"${myKey}\" ${str} order by a.create_time LIMIT ${pgIndex},${pgSize}")
	List<String> getFlowListByMyKeyStr(String myKey, String deptName, int pgIndex, int pgSize, String str);
	@Select("SELECT count(a.flow_id) FROM yy_data_info a LEFT JOIN (" +
		"SELECT * FROM (SELECT a.business_key,MAX(IF( a.json_key = 'gkbm', a.json_val, NULL )) AS 'gkbm',MAX(IF( a.json_key = 'xmmc'," +
		" a.json_val, NULL )) AS 'xmmc',MAX(IF( a.json_key = 'xmdj', a.json_val, NULL )) AS 'xmdj'," +
		"MAX(IF( a.json_key = 'yjqdsj', a.json_val, NULL )) AS 'yjqdsj',MAX(IF( a.json_key = 'xmjl', a.json_val, NULL )) AS 'xmjl' " +
		"FROM pmp_basic_load a GROUP BY a.business_key) temp WHERE temp.gkbm = \"${deptName}\") b ON a.flow_id = b.business_key " +
		"WHERE a.source = \"${myKey}\" ${str}")
	int getFlowListCountByMyKeyStr(String myKey, String deptName, String str);

	@Select("SELECT a.flow_id FROM yy_data_info a LEFT JOIN (" +
		"SELECT * FROM (SELECT a.business_key,MAX(IF( a.json_key = 'gkbm', a.json_val, NULL )) AS 'gkbm',MAX(IF( a.json_key = 'xmmc'," +
		" a.json_val, NULL )) AS 'xmmc',MAX(IF( a.json_key = 'xmdj', a.json_val, NULL )) AS 'xmdj'," +
		"MAX(IF( a.json_key = 'yjqdsj', a.json_val, NULL )) AS 'yjqdsj',MAX(IF( a.json_key = 'xmjl', a.json_val, NULL )) AS 'xmjl' " +
		"FROM pmp_basic_load a GROUP BY a.business_key) temp WHERE temp.gkbm = \"${deptName}\") b ON a.flow_id = b.business_key " +
		"WHERE a.my_key = \"${myKey}\" ${str} LIMIT ${pgIndex},${pgSize}")
	List<String> getFlowListStr(String myKey, String deptName, int pgIndex, int pgSize, String str);
	@Select("SELECT count(a.flow_id) FROM yy_data_info a LEFT JOIN (" +
		"SELECT * FROM (SELECT a.business_key,MAX(IF( a.json_key = 'gkbm', a.json_val, NULL )) AS 'gkbm',MAX(IF( a.json_key = 'xmmc'," +
		" a.json_val, NULL )) AS 'xmmc',MAX(IF( a.json_key = 'xmdj', a.json_val, NULL )) AS 'xmdj'," +
		"MAX(IF( a.json_key = 'yjqdsj', a.json_val, NULL )) AS 'yjqdsj',MAX(IF( a.json_key = 'xmjl', a.json_val, NULL )) AS 'xmjl' " +
		"FROM pmp_basic_load a GROUP BY a.business_key) temp WHERE temp.gkbm = \"${deptName}\") b ON a.flow_id = b.business_key " +
		"WHERE a.my_key = \"${myKey}\" ${str}")
	int getFlowListCountStr(String myKey, String deptName, String str);

	/*@Select("select temp.flow_id from (select a.flow_id from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key" +
		" where a.my_key=\"${myKey}\" and b.json_key=\"xmjl\" and b.json_val=\"${getRealName}\" limit ${pgIndex},${pgSize} ) temp left join" +
		" pmp_basic_load b on temp.flow_id=b.business_key where json_key=\"gkbm\" and b.json_val=\"${departName}\"")*/
	@Select("SELECT a.flow_id FROM yy_data_info a LEFT JOIN (" +
		"SELECT * FROM (SELECT a.business_key,MAX(IF( a.json_key = 'gkbm', a.json_val, NULL )) AS 'gkbm',MAX(IF( a.json_key = 'xmmc'," +
		" a.json_val, NULL )) AS 'xmmc',MAX(IF( a.json_key = 'xmdj', a.json_val, NULL )) AS 'xmdj'," +
		"MAX(IF( a.json_key = 'yjqdsj', a.json_val, NULL )) AS 'yjqdsj',MAX(IF( a.json_key = 'xmjl', a.json_val, NULL )) AS 'xmjl' " +
		"FROM pmp_basic_load a GROUP BY a.business_key) temp WHERE temp.xmjl = \"${getRealName}\") b ON a.flow_id = b.business_key " +
		"WHERE a.my_key = \"${myKey}\" ${str} LIMIT ${pgIndex},${pgSize}")
	List<String> getFlowList1ByName(String myKey, String getRealName, int pgIndex, int pgSize, String str);
	/*@Select("select count(temp.flow_id) from (select a.flow_id from yy_data_info a LEFT JOIN pmp_basic_load b " +
		"on a.flow_id=b.business_key where a.my_key=\"${myKey}\" and b.json_key=\"xmjl\" and b.json_val=\"${getRealName}\") temp " +
		"left join pmp_basic_load b on temp.flow_id=b.business_key where json_key=\"gkbm\" and b.json_val=\"${departName}\"")*/
	@Select("SELECT count(a.flow_id) FROM yy_data_info a LEFT JOIN (" +
		"SELECT * FROM (SELECT a.business_key,MAX(IF( a.json_key = 'gkbm', a.json_val, NULL )) AS 'gkbm',MAX(IF( a.json_key = 'xmmc'," +
		" a.json_val, NULL )) AS 'xmmc',MAX(IF( a.json_key = 'xmdj', a.json_val, NULL )) AS 'xmdj'," +
		"MAX(IF( a.json_key = 'yjqdsj', a.json_val, NULL )) AS 'yjqdsj',MAX(IF( a.json_key = 'xmjl', a.json_val, NULL )) AS 'xmjl' " +
		"FROM pmp_basic_load a GROUP BY a.business_key) temp WHERE temp.xmjl = \"${getRealName}\") b ON a.flow_id = b.business_key " +
		"WHERE a.my_key = \"${myKey}\" ${str}")
	int getFlowList1CountByName(String myKey, String getRealName, String str);

	/**
	 * 员工查询数据
	 * @return
	 */

	@Select("select a.flow_id from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key where a.my_key=\"${myKey}\" " +
		"and b.json_key=\"${jsonKey}\" and b.json_val=\"${getRealName}\" order by a.create_time desc limit ${pgIndex},${pgSize}")
	List<String> getFlowList1(String myKey,String jsonKey, String getRealName,int pgIndex,int pgSize);

	@Select("select COALESCE(count(a.flow_id),0) from yy_data_info a LEFT JOIN pmp_basic_load b on a.flow_id=b.business_key" +
		" where a.my_key=\"${myKey}\" and b.json_key=\"${jsonKey}\" and b.json_val=\"${getRealName}\"")
	int getFlowList1Count(String myKey,String jsonKey, String getRealName);

	@Select("SELECT COALESCE(sum( ${key2} ),0.0) from" +
		"(select MAX(IF(a.json_key = '${key1}', json_val, NULL)) AS '${key1}',MAX(IF(a.json_key = '${key2}', json_val, NULL)) AS '${key2}'" +
		"from" +
		"(SELECT * FROM pmp_basic_load WHERE business_key =\"${businessKey}\" and place=\"${place}\") a " +
		"GROUP BY flow_id) temp where YEAR(temp.${key1})=${year}")
	Double getTableCountByKey(String businessKey, String key1,String key2,int year,String place);

	@Select("select COALESCE(sum(json_val),0) from pmp_basic_load where business_key in\n" +
		"(select business_key from pmp_basic_load where json_key=\"${timeKey}\" and YEAR(json_val)=${year}) and json_key=\"${key}\"")
	double getCountYear(String key, String timeKey,int year);

	@Select("select COALESCE(sum(json_val),0) from pmp_basic_load where flow_id in\n" +
		"(select flow_id from pmp_basic_load where json_key=\"${timeKey}\" and YEAR(json_val)=${year}) and json_key=\"${key}\"")
	double getCountYearByFlowId(String key, String timeKey, int year);

	@Select("select flow_id from pmp_basic_load where json_key=\"${key}\" and YEAR(json_val)=${year}")
	List<String> listByFlow(String key,int year);

	@Select("select MAX(IF(json_key = 'xmlx', json_val, NULL)) AS  xmlx,MAX(IF(json_key = 'gkbm', json_val, NULL)) AS  gkbm from pmp_basic_load\n" +
		"where business_key =\"${business_key}\"")
	JSONObject getInfo(String business_key);

	@Select("select json_val,business_key from pmp_basic_load WHERE flow_id=\"${flow_id}\" and json_key=\"${key}\"")
	JSONObject getBusinessKey(String key,String flow_id);

	@Select("select flow_id from pmp_basic_load where json_key=\"${key}\" and YEAR(json_val)=${year}" +
		" and business_key in(select business_key from pmp_basic_load where json_val=\"${deptName}\")")
	List<String> listByFlowByDePartName(String key, int year, String deptName);

	@Select("select COALESCE(sum(json_val),0) from pmp_basic_load where flow_id in (select flow_id from " +
		"pmp_basic_load where json_key=\"${timeKey}\" and YEAR(json_val)=${year} and business_key in (select business_key from pmp_basic_load where json_val=\"${deptName}\")) and json_key=\"${key}\"")
	double getCountYearByFlowIdByDeptName(String deptName,String key, String timeKey, int year);

	/**
	 * 根据flowId查询项目值
	 * @param flowId
	 * @return
	 */
	@Select("select json_val from pmp_basic_load where json_key=\'${key}\' and business_key=(select DISTINCT business_key from pmp_basic_load where flow_id=\'${flowId}\')")
    String getPmpValue(String key,String flowId);

	@Select("select json_val from pmp_basic_load where json_key=\'${key}\' and business_key=\'${flowId}\'")
	String getPmpValueByBus(String key,String flowId);

	/**
	 * 根据businessKey和jsonkey查询数据
	 * @param jsonKey
	 * @param businessKey
	 * @return
	 */
	@Select("select COALESCE(sum(json_val),0) from pmp_basic_load where json_key=\"${jsonKey}\" and business_key = \"${businessKey}\"")
	double getCountMoneyByMyKeyAndBus(String jsonKey, String businessKey);
}
